
from string import Template
from tack.lib import string_app, json_app, content_headers, status_codes
from datetime import datetime
from util import build_row_dict, state_def


class Sql:
    select_details_by_commitment_id = """
    SELECT tc.commitment_id,
           tcd.commitment_detail_id,
           tcd.requested_by,
           tcd.requester_phone,
           tcd.trans_coord_name,
           tcd.trans_coord_phone,
           tcd.org_req_name,
           tcd.org_req_phone,
           tcd.purpose,
           tcd.poc_name,
           tcd.poc_phone,
           tcd.poc_alt_phone,
           tcd.poc_smtp,
           tcd.deliver_post,
           tcd.wait,
           tcd.fund_cite
    FROM tmpo_commitment tc
        LEFT OUTER JOIN tmpo_commitment_detail tcd
            ON tc.commitment_id = tcd.commitment_id
    WHERE tc.commitment_id = $commitment_id
        AND tcd.state_key <> 4
        AND tcd.state_finish IS NULL
    """


    select_commmitment_by_state = """
    SELECT tc.commitment_id
    FROM tmpo_commitment tc
        LEFT OUTER JOIN tmpo_commitment_detail tcd
            ON tc.commitment_id = tcd.commitment_id
    WHERE tcd.state_key = $state_key
        AND tcd.state_finish IS NULL
    """


    select_commitment_by_date = """
    SELECT DISTINCT temp.commitment_id FROM 
    (
    SELECT tc.commitment_id AS commitment_id
    FROM tmpo_commitment tc
        LEFT OUTER JOIN tmpo_commitment_detail tcd
            ON tc.commitment_id = tcd.commitment_id
        LEFT OUTER JOIN tmpo_commitment_leg tcl
            ON tc.commitment_id = tcl.commitment_id
    WHERE CONVERT(CHAR(10),tcl.start,121) = '$working_date'
        AND tcd.state_key = 3
        AND tcd.state_finish IS NULL
    ) AS temp
    """


    select_legs_by_commitment_id = """
    SELECT tcl.commitment_leg_id,
           tcl.checkin,
           tcl.start,
           tcl.finish,
           tcl.location,
           tcl.destination,
           tcl.passengers,
           tcl.cargo,
           tcl.comment
    FROM tmpo_commitment_leg tcl
    WHERE tcl.commitment_id = $commitment_id
    """


    select_drivers_by_commitment_leg_id = """
    SELECT tcld.driver_id
    FROM tmpo_commitment_leg_driver tcld
    WHERE tcld.commitment_leg_id = $commitment_leg_id
    """
    

    select_vehicles_by_commitment_leg_id = """
    SELECT tclv.vehicle_id
    FROM tmpo_commitment_leg_vehicle tclv
    WHERE tclv.commitment_leg_id = $commitment_leg_id
    """


def get_by_commitment_id(environ, start_response):
    commitment_id = environ['dispatcher.groups']['commitment_id']
    output = build_commitment(environ, commitment_id)
    if len(output.keys()) == 0:
        start_response(status_codes.not_found,
                       content_headers.default_plain())
        return ['404 Not Found']
    return json_app(output)(environ, start_response)


def get_requested(environ, start_response):
    output = get_by_state(environ, state_def.REQUESTED)
    return json_app(output)(environ, start_response)


def get_conflicted(environ, start_response):
    output = get_by_state(environ, state_def.CONFLICTED)
    return json_app(output)(environ, start_response)


def get_by_date(environ, start_response):
    try:
        working_date = environ['dispatcher.groups']['date']
        throwaway = datetime.strptime(working_date,'%Y-%m-%d')
    except:
        start_response(status_codes.error, content_headers.default_plain())
        return ['Error']
    sql = Sql.select_commitment_by_date
    sql = Template(sql).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    output = []
    for row in rows:
        output.append(build_commitment(environ, row.commitment_id))
    output.sort(key=leg_sort_key)
    return json_app(output)(environ, start_response)



def leg_sort_key(v):
    try:
        d = v['legs'][0]['start']
        return datetime.strptime(d, "%Y-%m-%d %H:%M:%S")
    except (IndexError, ):
        return datetime(1990,1,1)


def get_by_state(environ, skey):
    state_key = skey
    sql = Sql.select_commmitment_by_state
    sql = Template(sql).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    output = []
    for row in rows:
        output.append(build_commitment(environ, row[0]))
    return output


def build_commitment(environ, commitment_id):
    commitment_id = commitment_id
    sql = Sql.select_details_by_commitment_id
    sql = Template(sql).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    rows_cd = cursor.description
    if rows:
        output = build_row_dict(rows_cd, rows[0])
        sql = Sql.select_legs_by_commitment_id
        sql = Template(sql).substitute(locals())
        cursor.execute(sql)
        legs = cursor.fetchall()
        legs_cd = cursor.description
        output['legs'] = []
        for leg in legs:
            leg_stuff = build_row_dict(legs_cd, leg)
            commitment_leg_id = leg.commitment_leg_id
            sql = Sql.select_drivers_by_commitment_leg_id
            sql = Template(sql).substitute(locals())
            cursor.execute(sql)
            drivers = cursor.fetchall()
            leg_stuff['drivers'] = []
            for driver in drivers:
                leg_stuff['drivers'].append(driver.driver_id)
            sql = Sql.select_vehicles_by_commitment_leg_id
            sql = Template(sql).substitute(locals())
            cursor.execute(sql)
            vehicles = cursor.fetchall()
            leg_stuff['vehicles'] = []
            for vehicle in vehicles:
                leg_stuff['vehicles'].append(vehicle.vehicle_id)           
            output['legs'].append(leg_stuff)
    else:
        output = {}
    return output
